﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace webtracuudiem.Truyxuatdulieu
{
    public class TienIchSql
    {
        public static string LayChuoiKetNoi()
        {
            return WebConfigurationManager.ConnectionStrings["DatabaseDemo1"].ConnectionString;
            //Chuoi "DatabaseDemo1" được lấy từ file Web.config tại add name="DatabaseDemo1" connectionString="Data Source=.;Initial Catalog=Demo1;Integrated Security=True;Pooling=False"
            //Để có được chuỗi trong connectionString ta Click phải vào Database trong SQL Server > chọn Properties > copy chuỗi connectionString dán vào cho chính xác  
        }
        public static int ChayLenhCapNhatDuLieu(string LenhSQL)
        {
            using (SqlConnection cnn = new SqlConnection())
            {
                cnn.ConnectionString = LayChuoiKetNoi();
                cnn.Open(); //Mo ket noi
                SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
                cmd.CommandText = LenhSQL;
                return cmd.ExecuteNonQuery();
            }
        }
        public static int ChayLenhCapNhatDuLieu(string LenhSQL, string[] arrTenThamSo, object[] arrGiaTriThamSo)
        {
            using (SqlConnection cnn = new SqlConnection())
            {
                cnn.ConnectionString = LayChuoiKetNoi();
                cnn.Open(); //Mo ket noi
                SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
                cmd.CommandText = LenhSQL;
                //Truyen tham so cho command
                for (int i = 0; i < arrTenThamSo.Length; i++)
                {
                    cmd.Parameters.AddWithValue(arrTenThamSo[i], arrGiaTriThamSo[i]);
                }
                return cmd.ExecuteNonQuery();
            }
        }
        public static int ChayLenhCapNhatDuLieu(string LenhSQL, string[] arrTenThamSo, object[] arrGiaTriThamSo,SqlDbType[] kieuThamSo)
        {
            using (SqlConnection cnn = new SqlConnection())
            {
                cnn.ConnectionString = LayChuoiKetNoi();
                cnn.Open(); //Mo ket noi
                SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
                cmd.CommandText = LenhSQL;
                //Truyen tham so cho command
                for (int i = 0; i < arrTenThamSo.Length; i++)
                {
                    SqlParameter p = new SqlParameter(arrTenThamSo[i],kieuThamSo[i]);
                    p.IsNullable = true;
                    if (arrGiaTriThamSo[i] == null)
                        p.Value = DBNull.Value;
                    else
                        p.Value = arrGiaTriThamSo[i];
                    cmd.Parameters.Add(p);
                }
                return cmd.ExecuteNonQuery();
            }
        }
        public static int ChayLenhCapNhatDuLieu(string lenhSql, params object[] thamSo)
        {
            using (SqlConnection cnn = new SqlConnection())
            {
                cnn.ConnectionString = LayChuoiKetNoi();
                cnn.Open(); //Mo ket noi
                SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
                cmd.CommandText = lenhSql;
                //Truyen tham so cho command
                for (int i = 0; i < thamSo.Length - 1; i += 2)
                {
                    cmd.Parameters.AddWithValue(thamSo[i].ToString(), thamSo[i + 1]);
                }
                return cmd.ExecuteNonQuery();
            }
        }

        public static DataTable ChayLenhSelectDuLieu(string lenhSqlSelect, params object[] thamSo)
        {
            using (SqlConnection cnn = new SqlConnection())
            {
                cnn.ConnectionString = LayChuoiKetNoi();
                cnn.Open(); //Mo ket noi
                SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
                cmd.CommandText = lenhSqlSelect;
                //Truyen tham so cho command
                for (int i = 0; i < thamSo.Length - 1; i += 2)
                {
                    cmd.Parameters.AddWithValue(thamSo[i].ToString(), thamSo[i + 1]);
                }
                SqlDataReader reader = cmd.ExecuteReader();
                DataTable tblKetQua = new DataTable();
                tblKetQua.Load(reader);
                return tblKetQua;
            }
        }
    }
}
      